﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Xml;
using System.Web;
using System.Web.UI.WebControls;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.Data.Sql;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
namespace sonicERPinterfaceCsharp.Forms
{
    public class Util
    {
        public void gerarxml(DataView dv)
        {
            try
            {

                System.Data.DataTable dt = dv.ToTable();

                XmlTextWriter xml = new XmlTextWriter(@"c:\gerar.xml", Encoding.UTF8);
                xml.WriteStartDocument();
                xml.WriteStartElement("teste");


                foreach (DataColumn item in dt.Columns)
                {

                    item.ColumnName = item.ColumnName.TrimEnd();
                    item.ColumnName = item.ColumnName.TrimStart();
                    item.ColumnName = item.ColumnName.Trim();
                    item.ColumnName = item.ColumnName.Replace(" ", "_");
                }

               
                foreach (DataRow item in dt.Rows)
                {

                    xml.WriteStartElement("tabela");
                    for (int y = 0; y < dt.Columns.Count; y++)
                    {
                        xml.Formatting = Formatting.Indented;
                        xml.WriteElementString(dt.Columns[y].ColumnName, item[y].ToString());
                    }
                    xml.WriteEndElement();
                }
                xml.WriteFullEndElement();
                xml.WriteEndDocument();

                xml.Close();
            }
            catch (Exception EX)
            {

                throw new Exception(EX.Message);
            }
        }
         public string caminhoxml = @"c:\gerar.xml";

        
         public System.Data.DataTable  getxml()
         {


             System.Data.DataTable dt = new System.Data.DataTable();
             DataSet ds = new DataSet("XMLDataSet");
             if (System.IO.File.Exists(caminhoxml))
             {


                 XmlDocument xml = new XmlDocument();
                 xml.Load(caminhoxml);
                 ds.ReadXml(caminhoxml);
                
             }
             else
             {
                 throw new Exception("Arquivo XML não Existe");
             }
             dt = ds.Tables[0];
             return (dt );
         }


         public static void ExportToExcel(DataGridView dgView, string fileName)
         {

             Microsoft.Office.Interop.Excel.Application excelApp = null;

             try
             {

                 // instantiating the excel application class

                 excelApp = new Microsoft.Office.Interop.Excel.Application();

                 Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing);

                 Worksheet currentWorksheet = (Worksheet)currentWorkbook.ActiveSheet;

                 int i = 1;

                 currentWorksheet.Columns.ColumnWidth = 18;

                 foreach (DataGridViewColumn dgviewColumn in dgView.Columns)
                 {

                     // Excel work sheet indexing starts with 1

                     currentWorksheet.Cells[1, i] = dgviewColumn.Name;

                     ++i;

                 }

                 Range headerColumnRange = currentWorksheet.get_Range("A1", "G1");

                 headerColumnRange.Font.Bold = true;

                 headerColumnRange.Font.Color = 0xFF0000;

                 //headerColumnRange.EntireColumn.AutoFit();

                 int rowIndex = 0;

                 for (rowIndex = 0; rowIndex < dgView.Rows.Count; rowIndex++)
                 {

                     DataGridViewRow dgRow = dgView.Rows[rowIndex];

                     for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
                     {

                         currentWorksheet.Cells[rowIndex + 2, cellIndex + 1] = dgRow.Cells[cellIndex].Value;

                     }

                 }

                 Range fullTextRange = currentWorksheet.get_Range("A1", "G" + (rowIndex + 1).ToString());

                 fullTextRange.WrapText = true;

                 fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

                 string fullFileName = Environment.CurrentDirectory;

                 fullFileName = fullFileName + "\\" + fileName;

                 currentWorkbook.SaveCopyAs(fullFileName);

                 // indicating that we already saved the workbook, otherwise call to Quit() will pop up

                 // the save file dialogue box

                 currentWorkbook.Saved = true;

                 MessageBox.Show("Datagrid view exported to file " + fullFileName, "Exported to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);

             }

             catch (Exception ex)
             {

                 MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);

             }

             finally
             {

                 if (excelApp != null)
                 {

                     excelApp.Quit();

                 }
                 
             }

         }

        // exemplo de envento

         
        

         private System.Data.DataTable CreateTestTable(string p)
        {

            throw new NotImplementedException();
        }

    }
  
     
    
}
